﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Data;

namespace Calc
{
    class ProjectDB
    {
        ManageItems objManageItems = new ManageItems();
        SqlCeConnection cn = new SqlCeConnection();
        public static int ProjectID = 0;
        public static int MeasurementID = 0;
        public static int ID = 0;
        public static string Desctiption = "";
        public static string Coverage = "";

        public DataSet GetProjectDetails(ProjectEntities objProjectEntities)
        {
            DataSet ds = new DataSet();
            SqlCeConnection cn = new SqlCeConnection(objManageItems.GetConnectionString());
            SqlCeCommand cmd = new SqlCeCommand();
            if (objProjectEntities.ProjectID == 0)
                cmd.CommandText = "Select ProjectID,ProjectName,Status from ProjectDetails";
            else
                cmd.CommandText = "Select * from ProjectDetails where ProjectID=" + objProjectEntities.ProjectID;
            cmd.Connection = cn;
            SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
            da.Fill(ds);

            return ds;
        }

        public void ChangeProjectStatus(ProjectEntities objProjectEntities)
        {
            SqlCeConnection cn = new SqlCeConnection(objManageItems.GetConnectionString());
            SqlCeCommand cmd = new SqlCeCommand();
            if (objProjectEntities.Action == 0)
                cmd.CommandText = "Update ProjectDetails set Status=1 where ProjectID=" + objProjectEntities.ProjectID;
            else if (objProjectEntities.Action == 1)
                cmd.CommandText = "Update ProjectDetails set Status=0 where ProjectID=" + objProjectEntities.ProjectID;
            cmd.Connection = cn;

            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        public void ManageProjectDetails(ProjectEntities objProjectEntities)
        {
            SqlCeConnection cn = new SqlCeConnection(objManageItems.GetConnectionString());
            SqlCeCommand cmd = new SqlCeCommand();
            if (objProjectEntities.ProjectID == 0)
                cmd.CommandText = "insert into ProjectDetails (ProjectName,Address,City,State,Zip,Phone,Fax,Notes,DnyLbl1,DnyLbl2,DnyLbl3,DnyLbl4,DnyLbl5,"
                    + "txtDny1,txtDny2,txtDny3,txtDny4,txtDny5,IsDeleted,status) values ('"
                    + objProjectEntities.ProjectName + "','" + objProjectEntities.Address + "','" + objProjectEntities.City + "','" + objProjectEntities.State + "','"
                    + objProjectEntities.Zip + "','" + objProjectEntities.Phone + "','" + objProjectEntities.Fax + "','"
                    + objProjectEntities.Notes + "','" + objProjectEntities.DnyLbl1 + "','" + objProjectEntities.DnyLbl2 + "','" + objProjectEntities.DnyLbl3
                    + "','" + objProjectEntities.DnyLbl4 + "','" + objProjectEntities.DnyLbl5 + "','" + objProjectEntities.TxtDny1 + "','" + objProjectEntities.TxtDny2
                    + "','" + objProjectEntities.TxtDny3 + "','" + objProjectEntities.TxtDny4 + "','" + objProjectEntities.TxtDny5 + "',0,0)";
            else
                cmd.CommandText = "Update ProjectDetails set ProjectName='" + objProjectEntities.ProjectName + "', Address='" + objProjectEntities.Address
                    + "', City='" + objProjectEntities.City + "', State='" + objProjectEntities.State + "', Zip='" + objProjectEntities.Zip + "',"
                    + "Phone='" + objProjectEntities.Phone + "', Fax='" + objProjectEntities.Fax + "', Notes='" + objProjectEntities.Notes
                    + "',txtDny1='" + objProjectEntities.TxtDny1 + "',txtDny2='" + objProjectEntities.TxtDny2 + "',txtDny3='" + objProjectEntities.TxtDny3
                    + "',txtDny4='" + objProjectEntities.TxtDny4 + "',txtDny5='" + objProjectEntities.TxtDny5 + "',DnyLbl1='" + objProjectEntities.DnyLbl1
                    + "',DnyLbl2='" + objProjectEntities.DnyLbl2 + "',DnyLbl3='" + objProjectEntities.DnyLbl3 + "',DnyLbl4='" + objProjectEntities.DnyLbl4
                    + "',DnyLbl5='" + objProjectEntities.DnyLbl5 + "' where ProjectID=" + objProjectEntities.ProjectID;

            cmd.Connection = cn;
            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        public void ManageMeasurements(ProjectEntities objProjectEntities)
        {
            SqlCeConnection cn = new SqlCeConnection(objManageItems.GetConnectionString());
            cn.Open();
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = cn;
            //cmd.CommandText = "delete from Measurements where ProjectID=" + objProjectEntities.ProjectID;
            //cmd.ExecuteNonQuery(); 
            if (objProjectEntities.Action == 1)
            {
                cmd.CommandText = "insert into Measurements (ProjectID,Description,OriginalMeasurement,CorrectedMeasurement,MeasurementUnits,HavingChild,IsDeleted) values ("
                    + objProjectEntities.ProjectID + ",'" + objProjectEntities.Description + "'," + objProjectEntities.Measurement + "," + objProjectEntities.Measurement
                    + ",'" + objProjectEntities.MeasurementUnits + "',0,0)";
            }
            else if (objProjectEntities.Action == 0)
            {
                cmd.CommandText = "Update Measurements set Description='" + objProjectEntities.Description + "',OriginalMeasurement=" + objProjectEntities.Measurement
                    + ",CorrectedMeasurement=" + objProjectEntities.Measurement + ",MeasurementUnits='" + objProjectEntities.MeasurementUnits
                    + "' where MeasurementID=" + objProjectEntities.MeasurementID;
            }
            cmd.Connection = cn;

            cmd.ExecuteNonQuery();
            cn.Close();
        }
        public DataSet GetMeasurements(ProjectEntities objProjectEntities)
        {
            SqlCeConnection cn = new SqlCeConnection(objManageItems.GetConnectionString());
            SqlCeDataAdapter ada = new SqlCeDataAdapter("Select Description,OriginalMeasurement as Measurement,MeasurementUnits,MeasurementID"
                 +" from Measurements where IsDeleted=0 and ProjectID=" + objProjectEntities.ProjectID, cn);
            DataSet ds = new DataSet();
            ada.Fill(ds);
            return ds;
        }

        public DataSet LoadMeasurements(ProjectEntities objProjectEntities)
        {
            SqlCeConnection cn = new SqlCeConnection(objManageItems.GetConnectionString());
            SqlCeDataAdapter ada = new SqlCeDataAdapter("Select Description,OriginalMeasurement as Measurement,CorrectedMeasurement,Note,MeasurementUnits as MeasurementUnit,"
                + "Layers,'' as Coverage,'' as Waste,'' as Units,'' as UnitType,'' as CostPerUnit,'' as Material,"
                + "'' as Labor,'' as Equipment,MeasurementID,ProjectID,HavingChild,"
                + "0 as ItemID,'' as ItemName,0 as GroupID,'' as MateiralName,0 as Cost,"
                + "'' as Labour,''as LabourType,0 as ID from Measurements where ProjectID=" + objProjectEntities.ProjectID, cn);
            DataSet ds = new DataSet();
            ada.Fill(ds);
            return ds;

        }
        public DataSet LoadMeasurementsByMeasurementID()
        {
            SqlCeConnection cn = new SqlCeConnection(objManageItems.GetConnectionString());
            SqlCeDataAdapter ada = new SqlCeDataAdapter("Select Description,OriginalMeasurement,CorrectedMeasurement,Note,MeasurementUnits,"
                + "Layers,'' as Coverage,'' as Waste,'' as PurchaseUnits,'' as PurchaseUnitType,'' as CostperPurchaseUnit,'' as Material,"
                + "'' as Labor,'' as Equipment,MeasurementID,ProjectID,HavingChild,"
                + "0 as ItemID,'' as ItemName,0 as GroupID,'' as MateiralName,0 as Cost,"
                + "'' as Labour,''as LabourType,0 as ID from Measurements where MeasurementID=" + ProjectDB.MeasurementID, cn);
            DataSet ds = new DataSet();
            ada.Fill(ds);
            return ds;

        }

        public DataSet GetMeasurementChilds(ProjectEntities objProjectEntities)
        {
            SqlCeConnection cn = new SqlCeConnection(objManageItems.GetConnectionString());
            SqlCeDataAdapter ada = new SqlCeDataAdapter("Select MC.ItemName+'    '+ MT.MaterialName as Description,MC.OriginalMeasurement as Measurement,"
                + "MC.CorrectedMeasurement,MC.Note,MC.MeasurementUnits as MeasurementUnit,MC.Layers,MC.Coverage,MC.Waste,'' as Units,"
                + "MC.CostPer as UnitsType,MC.Cost as CostPerUnit,'' as Material,'' as Labor,'' as Equipment,"
                + "MC.MeasurementID,0 as ProjectID,"
                + "0 as HavingChild,MC.ItemID,MC.ItemName,MC.GroupID,MT.MaterialName,MC.Cost,"
                + "MC.Labour,MC.LabourType,MC.ID from MeasurementChild MC,MaterialType MT where MC.MaterialTypeID=MT.MaterialTypeID", cn);
            DataSet ds = new DataSet();
            ada.Fill(ds);
            return ds;
        }

        public DataSet GetMeasurementChildsByID(int ID)
        {
            SqlCeConnection cn = new SqlCeConnection(objManageItems.GetConnectionString());
            SqlCeDataAdapter ada = new SqlCeDataAdapter("Select MC.ItemName+'    '+ MT.MaterialName as Description,MC.OriginalMeasurement,"
                + "MC.CorrectedMeasurement,MC.Note,MC.MeasurementUnits,MC.Layers,MC.Coverage,MC.Waste,'' as PurchaseUnits,"
                + "MC.CostPer as PurchaseUnitType,MC.Cost as CostperPurchaseUnit,'' as Material,'' as Labor,'' as Equipment,"
                + "MC.MeasurementID,0 as ProjectID,"
                + "0 as HavingChild,MC.ItemID,MC.ItemName,MC.GroupID,MT.MaterialName,MC.Cost,"
                + "MC.Labour,MC.LabourType,MC.ID from MeasurementChild MC,MaterialType MT where ID=" + ID + " and MC.MaterialTypeID=MT.MaterialTypeID", cn);
            DataSet ds = new DataSet();
            ada.Fill(ds);
            return ds;
        }

        public void DeleteMeasurements(ProjectEntities objProjectEntities)
        {
            SqlCeConnection cn = new SqlCeConnection(objManageItems.GetConnectionString());
            cn.Open();
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = cn;
            cmd.CommandText = "delete from Measurements where ProjectID=" + objProjectEntities.ProjectID;
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        public void ManageMeasurementChild(int ID, int MeasurementID, int OriginalMeasurement, int CorrectedMeasurement, string Note, string MeasurementUnits, int Layers, int ItemID, string ItemName, int GroupID, int MaterialTypeID, decimal Cost, string CostPer, string Coverage, string Waste, string Labour, int LabourType, int Action)
        {
            SqlCeConnection cn = new SqlCeConnection(objManageItems.GetConnectionString());
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = cn;
            //"Create Table MeasurementChild (ID int primary key identity(1,1),MeasurementID int,OrigianlMeasurement int,CorrectedMeasurement int,Note nvarchar(2000),MeasurementUnits nvarchar(50),Layers nvarchar(100),ItemID int,ItemName nvarchar(100),GroupID int,MaterialTypeID int," +
            //"Cost money,CostPer nvarchar(100),Coverage nvarchar(100),Waste  nvarchar(100),Labour nvarchar(100),LabourType nvarchar(100)," +
            //"CreatedDate Datetime,IsDeleted bit)"
            if (Action == 0)
            {
                cmd.CommandText = "insert into MeasurementChild (MeasurementID,OriginalMeasurement,CorrectedMeasurement,Note,MeasurementUnits,"
                    + "Layers,ItemID,ItemName,GroupID,MaterialTypeID,Cost,CostPer,Coverage,Waste,Labour,LabourType,IsDeleted) values"
                    + "(" + MeasurementID + "," + OriginalMeasurement + "," + CorrectedMeasurement + ",'" + Note + "','" + MeasurementUnits
                    + "',1," + ItemID + ",'" + ItemName + "'," + GroupID + "," + MaterialTypeID + "," + Cost + ",'" + CostPer
                    + "','" + Coverage + "','" + Waste + "','" + Labour + "'," + LabourType + ",0)";
            }

            if (Action == 1)
            {
                cmd.CommandText = "update MeasurementChild set OriginalMeasurement=" + OriginalMeasurement + ", CorrectedMeasurement="
                    + CorrectedMeasurement + ",Layers=" + Layers + ",ItemName='" + ItemName + "',Cost=" + Cost + ",Coverage='" + Coverage
                    + "',Waste='" + Waste + "',Labour='" + Labour + "' where ID=" + ID;
            }
            cn.Open();
            cmd.ExecuteNonQuery();

            cmd.CommandText = "Update Measurements set HavingChild=1 where MeasurementID=" + MeasurementID;
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        public DataSet ManageConcreteCalculator(CalculatorEntities objCalculatorEntities)
        {
            SqlCeConnection cn = new SqlCeConnection(objManageItems.GetConnectionString());
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.Connection = cn;
            if (objCalculatorEntities.Action == 0)
            {
                cmd.CommandText = "Select * from ConcreteCalculator where MeasurementChildID=" + ProjectDB.ID;
            }
            else if (objCalculatorEntities.Action == 1)
            {
                cmd.CommandText = "Insert into ConcreteCalculator (MeasurementChildID,SquareInches,CubicYards,Width,Height1,Height2,Height3,"
                    + "Height4,Height5,Height6,Height7,Height8,Height9,Height10,Height11,Height12,CubicYards1,Diameter,CubicYards2,"
                    + "Length1,Width1,Height,CubicYards3) values (" + ProjectDB.ID + ",'" + objCalculatorEntities.SquareInches + "','"
                    + objCalculatorEntities.CubicYards + "','" + objCalculatorEntities.Width + "','" + objCalculatorEntities.Height1 + "','"
                    + objCalculatorEntities.Height2 + "','" + objCalculatorEntities.Height3 + "','" + objCalculatorEntities.Height4 + "','"
                    + objCalculatorEntities.Height5 + "','" + objCalculatorEntities.Height6 + "','" + objCalculatorEntities.Height7 + "','"
                    + objCalculatorEntities.Height8 + "','"
                    + objCalculatorEntities.Height9 + "','" + objCalculatorEntities.Height10 + "','" + objCalculatorEntities.Height11 + "','"
                    + objCalculatorEntities.Height12 + "','" + objCalculatorEntities.CubicYards1 + "','" + objCalculatorEntities.Diameter + "','"
                    + objCalculatorEntities.CubicYards2 + "','" + objCalculatorEntities.Length1 + "','" + objCalculatorEntities.Width1 + "','"
                    + objCalculatorEntities.Height + "','" + objCalculatorEntities.CubicYards3 + "')";
            }
            else if (objCalculatorEntities.Action == 2)
            {
                cmd.CommandText = "Update ConcreteCalculator set SquareInches='" + objCalculatorEntities.SquareInches + "',CubicYards='"
                    + objCalculatorEntities.CubicYards + "',Width='" + objCalculatorEntities.Width + "',Height1='" + objCalculatorEntities.Height1
                    + "',Height2='" + objCalculatorEntities.Height2 + "',Height3='" + objCalculatorEntities.Height3 + "',Height4='"
                    + objCalculatorEntities.Height4 + "',Height5='" + objCalculatorEntities.Height5 + "',Height6='" + objCalculatorEntities.Height6
                    + "',Height7='" + objCalculatorEntities.Height7 + "',Height8='" + objCalculatorEntities.Height8 + "',Height9='"
                    + objCalculatorEntities.Height9 + "',Height10='" + objCalculatorEntities.Height10 + "',Height11='" + objCalculatorEntities.Height11
                    + "',Height12='" + objCalculatorEntities.Height12 + "',CubicYards1='" + objCalculatorEntities.CubicYards1 + "',Diameter='"
                    + objCalculatorEntities.Diameter + "',CubicYards2='" + objCalculatorEntities.CubicYards2 + "',Length1='" + objCalculatorEntities.Length1
                    + "',Width1='" + objCalculatorEntities.Width1 + "',Height='" + objCalculatorEntities.Height + "',CubicYards3='"
                    + objCalculatorEntities.CubicYards3 + "' where MeasurementChildID=" + objCalculatorEntities.MeasurementChildID;

            }
            SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }

        public DataSet GetMiscItems(ProjectEntities objProjectEntities)
        {
            SqlCeConnection cn = new SqlCeConnection(objManageItems.GetConnectionString());
            DataSet ds = new DataSet();
            SqlCeCommand cmd = new SqlCeCommand("Select MiscItemName,MiscItemCost,MiscItemID,ProjectID from MiscItems where ProjectID="+ProjectDB.ProjectID, cn);
            SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
            da.Fill(ds);
            return ds;
            
        }
        public void ManageMiscItems(ProjectEntities objProjectEntities)
        {
            SqlCeConnection cn = new SqlCeConnection(objManageItems.GetConnectionString());
            SqlCeCommand cmd = new SqlCeCommand();
            if (objProjectEntities.MiscItemID == 0)
            {
                cmd.CommandText = "Insert into MiscItems (MiscItemName,MiscItemCost,ProjectID) values ('"
                    + objProjectEntities.MiscItemName + "','" + objProjectEntities.MiscItemCost + "'," + objProjectEntities.ProjectID + ")";
            }
            else
            {
                cmd.CommandText = "Update MiscItems set MiscItemName='" + objProjectEntities.MiscItemName + "',MiscItemCost='"
                    + objProjectEntities.MiscItemCost + "' where MiscItemID=" + objProjectEntities.MiscItemID;
            }
            cmd.Connection = cn;
            cn.Open();
            cmd.ExecuteNonQuery();
            cn.Close();
        }
    }
}